﻿using NfsCommon;
using NfsCommon.DbHelper;
using NfsModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace NfsDAL
{
    /// <summary>
    /// 数据访问类:agency
    /// </summary>
    public partial class AgencyDAL
    {
        public AgencyDAL()
        { }
        #region  Method

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperMySQL.GetMaxID("id", "t_agency");
        }
        public bool UpdateAucode(string au_code,int id) {

            string sql = @"update t_agency set au_code='"+au_code+"' where id='"+id+"'";
            int i=DbHelperMySQL.ExecuteSql(sql);
            if (i > 0)
            {
                return true;
            }
            else
                return false;
        }
        public int GetIdentityID(string tableName)
        {
            return DbHelperMySQL.GetIdentityID(tableName);

        }
        
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_agency");
            strSql.Append(" where id=" + id + " ");
            return DbHelperMySQL.Exists(strSql.ToString());
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(NfsModel.agency model,out int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            if (model.account_id != null)
            {
                strSql1.Append("account_id,");
                strSql2.Append("" + model.account_id + ",");
            }
            if (model.parents_id != null)
            {
                strSql1.Append("parents_id,");
                strSql2.Append("" + model.parents_id + ",");
            }
            if (model.shState != null)
            {
                strSql1.Append("shState,");
                strSql2.Append("" + model.shState + ",");
            }
            if (model.category_id != null)
            {
                strSql1.Append("category_id,");
                strSql2.Append("" + model.category_id + ",");
            }
            if (model.level_id != null)
            {
                strSql1.Append("level_id,");
                strSql2.Append("" + model.level_id + ",");
            }
            if (model.name != null)
            {
                strSql1.Append("name,");
                strSql2.Append("'" + model.name + "',");
            }
            if (model.company_name != null)
            {
                strSql1.Append("company_name,");
                strSql2.Append("'" + model.company_name + "',");
            }
            if (model.level_name != null)
            {
                strSql1.Append("level_name,");
                strSql2.Append("'" + model.level_name + "',");
            }
            if (model.mobile != null)
            {
                strSql1.Append("mobile,");
                strSql2.Append("'" + model.mobile + "',");
            }
            if (model.contract_name != null)
            {
                strSql1.Append("contract_name,");
                strSql2.Append("'" + model.contract_name + "',");
            }
            if (model.qq != null)
            {
                strSql1.Append("qq,");
                strSql2.Append("'" + model.qq + "',");
            }
            if (model.wx_openid != null)
            {
                strSql1.Append("wx_openid,");
                strSql2.Append("'" + model.wx_openid + "',");
            }
            if (model.ww_code != null)
            {
                strSql1.Append("ww_code,");
                strSql2.Append("'" + model.ww_code + "',");
            }
            if (model.id_card != null)
            {
                strSql1.Append("id_card,");
                strSql2.Append("'" + model.id_card + "',");
            }
            if (model.shop_url != null)
            {
                strSql1.Append("shop_url,");
                strSql2.Append("'" + model.shop_url + "',");
            }
            if (model.valid != null)
            {
                strSql1.Append("valid,");
                strSql2.Append("" + model.valid + ",");
            }
            if (model.state != null)
            {
                strSql1.Append("state,");
                strSql2.Append("" + model.state + ",");
            }
            if (model.start_time != null)
            {
                strSql1.Append("start_time,");
                strSql2.Append("'" + model.start_time + "',");
            }
            if (model.end_time != null)
            {
                strSql1.Append("end_time,");
                strSql2.Append("'" + model.end_time + "',");
            }
            if (model.sq_img != null)
            {
                strSql1.Append("sq_img,");
                strSql2.Append("'" + model.sq_img + "',");
            }
            if (model.au_code != null)
            {
                strSql1.Append("au_code,");
                strSql2.Append("'" + model.au_code + "',");
            }
            if (model.share_state != null)
            {
                strSql1.Append("share_state,");
                strSql2.Append("" + model.share_state + ",");
            }

 
            strSql.Append("insert into t_agency(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                id = GetIdentityID("t_agency");
                return true;
            }
            else
            {
                id = -1;
                return false;
            }
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(NfsModel.agency model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_agency set ");
            if (model.account_id != null)
            {
                strSql.Append("account_id=" + model.account_id + ",");
            }
           
            if (model.parents_id != null)
            {
                strSql.Append("parents_id=" + model.parents_id + ",");
            }
            if (model.shState != null)
            {
                strSql.Append("shState=" + model.shState + ",");
            }
          
            if (model.category_id != null)
            {
                strSql.Append("category_id=" + model.category_id + ",");
            }
            
            if (model.level_id != null)
            {
                strSql.Append("level_id=" + model.level_id + ",");
            }
          
            if (model.name != null)
            {
                strSql.Append("name='" + model.name + "',");
            }
           
            if (model.company_name != null)
            {
                strSql.Append("company_name='" + model.company_name + "',");
            }
           
            if (model.level_name != null)
            {
                strSql.Append("level_name='" + model.level_name + "',");
            }
         
            if (model.mobile != null)
            {
                strSql.Append("mobile='" + model.mobile + "',");
            }
        
            if (model.contract_name != null)
            {
                strSql.Append("contract_name='" + model.contract_name + "',");
            }
            
            if (model.qq != null)
            {
                strSql.Append("qq='" + model.qq + "',");
            }
           
            if (model.wx_openid != null)
            {
                strSql.Append("wx_openid='" + model.wx_openid + "',");
            }
            
            if (model.ww_code != null)
            {
                strSql.Append("ww_code='" + model.ww_code + "',");
            }
          
            if (model.id_card != null)
            {
                strSql.Append("id_card='" + model.id_card + "',");
            }
           
            if (model.shop_url != null)
            {
                strSql.Append("shop_url='" + model.shop_url + "',");
            }
            
            if (model.valid != null)
            {
                strSql.Append("valid=" + model.valid + ",");
            }
           
            if (model.state != null)
            {
                strSql.Append("state=" + model.state + ",");
            }
           
            if (model.start_time != null)
            {
                strSql.Append("start_time='" + model.start_time + "',");
            }
           
            if (model.end_time != null)
            {
                strSql.Append("end_time='" + model.end_time + "',");
            }
           
            if (model.sq_img != null)
            {
                strSql.Append("sq_img='" + model.sq_img + "',");
            }
            if (model.au_code != null)
            {
                strSql.Append("au_code='" + model.au_code + "',");
            }
            if (model.share_state != null)
            {
                strSql.Append("share_state=" + model.share_state + ",");
            }


            if (model.salesmanid != null)
            {
                strSql.Append("salesmanid='" + model.salesmanid + "',");
            }
            if (model.salesmanname != null)
            {
                strSql.Append("salesmanname='" + model.salesmanname + "',");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where id=" + model.id + "");
            int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_agency ");
            strSql.Append(" where id=" + id + "");
            int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }		/// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_agency ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public NfsModel.agency GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  ");
            strSql.Append(" id,account_id,parents_id,category_id,shState,level_id,name,company_name,level_name,mobile,contract_name,qq,wx_openid,ww_code,id_card,shop_url,valid,state,start_time,end_time,sq_img,au_code,share_state,salesmanid,salesmanname");
            strSql.Append(" from t_agency ");
            strSql.Append(" where id=" + id + "");
            NfsModel.agency model = new NfsModel.agency();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        public agency GetModel2(int id, int auid)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ta.*,sq.start_time as austart_time,sq.end_time as auend_time from t_agency ta  left join t_sq_authorization sq on ta.id=sq.agency_id where ta.id=" + id + " and sq.id=" + auid);
          
            NfsModel.agency model = new NfsModel.agency();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return  DataRowToModel2(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public NfsModel.agency DataRowToModel(DataRow row)
        {
            NfsModel.agency model = new NfsModel.agency();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["account_id"] != null && row["account_id"].ToString() != "")
                {
                    model.account_id = int.Parse(row["account_id"].ToString());
                }
                if (row["parents_id"] != null && row["parents_id"].ToString() != "")
                {
                    model.parents_id = int.Parse(row["parents_id"].ToString());
                }
                if (row["shState"] != null && row["shState"].ToString() != "")
                {
                    model.shState = int.Parse(row["shState"].ToString());
                }
                if (row["category_id"] != null && row["category_id"].ToString() != "")
                {
                    model.category_id = int.Parse(row["category_id"].ToString());
                }
                if (row["level_id"] != null && row["level_id"].ToString() != "")
                {
                    model.level_id = int.Parse(row["level_id"].ToString());
                }
                if (row["name"] != null)
                {
                    model.name = row["name"].ToString();
                }
                if (row["company_name"] != null)
                {
                    model.company_name = row["company_name"].ToString();
                }
                if (row["level_name"] != null)
                {
                    model.level_name = row["level_name"].ToString();
                }
                if (row["mobile"] != null)
                {
                    model.mobile = row["mobile"].ToString();
                }
                if (row["contract_name"] != null)
                {
                    model.contract_name = row["contract_name"].ToString();
                }
                if (row["qq"] != null)
                {
                    model.qq = row["qq"].ToString();
                }
                if (row["wx_openid"] != null)
                {
                    model.wx_openid = row["wx_openid"].ToString();
                }
                if (row["ww_code"] != null)
                {
                    model.ww_code = row["ww_code"].ToString();
                }
                if (row["id_card"] != null)
                {
                    model.id_card = row["id_card"].ToString();
                }
                if (row["shop_url"] != null)
                {
                    model.shop_url = row["shop_url"].ToString();
                }
                if (row["valid"] != null && row["valid"].ToString() != "")
                {
                    model.valid = int.Parse(row["valid"].ToString());
                }
                if (row["state"] != null && row["state"].ToString() != "")
                {
                    model.state = int.Parse(row["state"].ToString());
                }
                if (row["start_time"] != null && row["start_time"].ToString() != "")
                {
                    model.start_time = DateTime.Parse(row["start_time"].ToString());
                }
                if (row["end_time"] != null && row["end_time"].ToString() != "")
                {
                    model.end_time = DateTime.Parse(row["end_time"].ToString());
                }
                if (row["sq_img"] != null)
                {
                    model.sq_img = row["sq_img"].ToString();
                }
                if (row["au_code"] != null)
                {
                    model.au_code = row["au_code"].ToString();
                }
                if (row["share_state"] != null && row["share_state"].ToString() != "")
                {
                    model.share_state = int.Parse(row["share_state"].ToString());
                }
                if (row["salesmanid"] != null && row["salesmanid"].ToString()!="")
                {
                    model.salesmanid =int.Parse(row["salesmanid"].ToString());
                }
                if (row["salesmanname"] != null && row["salesmanname"].ToString() != "")
                {
                    model.salesmanname = row["salesmanname"].ToString();
                }
            }
            return model;
        }
        public NfsModel.agency DataRowToModel2(DataRow row)
        {
            NfsModel.agency model = new NfsModel.agency();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["account_id"] != null && row["account_id"].ToString() != "")
                {
                    model.account_id = int.Parse(row["account_id"].ToString());
                }
                if (row["parents_id"] != null && row["parents_id"].ToString() != "")
                {
                    model.parents_id = int.Parse(row["parents_id"].ToString());
                }
                if (row["shState"] != null && row["shState"].ToString() != "")
                {
                    model.shState = int.Parse(row["shState"].ToString());
                }
                if (row["category_id"] != null && row["category_id"].ToString() != "")
                {
                    model.category_id = int.Parse(row["category_id"].ToString());
                }
                if (row["level_id"] != null && row["level_id"].ToString() != "")
                {
                    model.level_id = int.Parse(row["level_id"].ToString());
                }
                if (row["name"] != null)
                {
                    model.name = row["name"].ToString();
                }
                if (row["company_name"] != null)
                {
                    model.company_name = row["company_name"].ToString();
                }
                if (row["level_name"] != null)
                {
                    model.level_name = row["level_name"].ToString();
                }
                if (row["mobile"] != null)
                {
                    model.mobile = row["mobile"].ToString();
                }
                if (row["contract_name"] != null)
                {
                    model.contract_name = row["contract_name"].ToString();
                }
                if (row["qq"] != null)
                {
                    model.qq = row["qq"].ToString();
                }
                if (row["wx_openid"] != null)
                {
                    model.wx_openid = row["wx_openid"].ToString();
                }
                if (row["ww_code"] != null)
                {
                    model.ww_code = row["ww_code"].ToString();
                }
                if (row["id_card"] != null)
                {
                    model.id_card = row["id_card"].ToString();
                }
                if (row["shop_url"] != null)
                {
                    model.shop_url = row["shop_url"].ToString();
                }
                if (row["valid"] != null && row["valid"].ToString() != "")
                {
                    model.valid = int.Parse(row["valid"].ToString());
                }
                if (row["state"] != null && row["state"].ToString() != "")
                {
                    model.state = int.Parse(row["state"].ToString());
                }
                if (row["start_time"] != null && row["start_time"].ToString() != "")
                {
                    model.start_time = DateTime.Parse(row["start_time"].ToString());
                }
                if (row["end_time"] != null && row["end_time"].ToString() != "")
                {
                    model.end_time = DateTime.Parse(row["end_time"].ToString());
                }


                if (row["austart_time"] != null && row["austart_time"].ToString() != "")
                {
                    model.austart_time = DateTime.Parse(row["austart_time"].ToString());
                }
                if (row["auend_time"] != null && row["auend_time"].ToString() != "")
                {
                    model.auend_time = DateTime.Parse(row["auend_time"].ToString());
                }


                if (row["sq_img"] != null)
                {
                    model.sq_img = row["sq_img"].ToString();
                }
                if (row["au_code"] != null)
                {
                    model.au_code = row["au_code"].ToString();
                }
                if (row["share_state"] != null && row["share_state"].ToString() != "")
                {
                    model.share_state = int.Parse(row["share_state"].ToString());
                }
                if (row["salesmanid"] != null && row["salesmanid"].ToString() != "")
                {
                    model.salesmanid = int.Parse(row["salesmanid"].ToString());
                }
                if (row["salesmanname"] != null && row["salesmanname"].ToString() != "")
                {
                    model.salesmanname = row["salesmanname"].ToString();
                }
            }
            return model;
        }
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,account_id,parents_id,shState,category_id,level_id,name,company_name,level_name,mobile,contract_name,qq,wx_openid,ww_code,id_card,shop_url,valid,state,start_time,end_time,sq_img,au_code,share_state");
            strSql.Append(" FROM t_agency ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperMySQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM t_agency ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperMySQL.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM ( ");
            strSql.Append(" SELECT ROW_NUMBER() OVER (");
            if (!string.IsNullOrEmpty(orderby.Trim()))
            {
                strSql.Append("order by T." + orderby);
            }
            else
            {
                strSql.Append("order by T.id desc");
            }
            strSql.Append(")AS Row, T.*  from t_agency T ");
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                strSql.Append(" WHERE " + strWhere);
            }
            strSql.Append(" ) TT");
            strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
            return DbHelperMySQL.Query(strSql.ToString());
        }

        /*
        */

        #endregion  Method
        #region  自定义方法

        #region 首页显示(根据当前登录人的ID来显示)
        public agency GetMeById(int accountId)
        {
            string strSql = string.Format(@"select * from  t_agency where account_id={0}", accountId);
            DataSet ds = DbHelperMySQL.Query(strSql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 当前登录人下级已审核和未审核的信息
        public DataTable GetMeNews(int accountId, string stateName, int pageindex, int pagesize)
        {
            pageindex = (pageindex - 1) * pagesize;
            string strSql = string.Format(@"select * from (select *, case state when 0 then '未审核'
            when 1 then '已审核'
            when 2 then '未通过' end as states 
            from t_agency  where parents_id in(select id from t_agency  where account_id={0})) as sta where states='{1}' and valid=1", accountId, stateName);
            strSql += "  where id between " + pageindex + "," + pagesize + " ";
            return DbHelperMySQL.Query(strSql).Tables[0];
        }
        #endregion

        #region 通过和不通过当前登录人的下级申请
        public bool UptPassOrNo(int id, int state, string levelName)
        {
            string strSql = string.Format(@"update t_agency set state={0} , level_name='{1}' where id in({2})", state, levelName, id);
            int row = DbHelperMySQL.ExecuteSql(strSql);
            if (row > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        #endregion

        #region PC模糊查询
        public DataTable GetAgencyBycondtion(int pageindex, int pagesize,int keys, string keyName, int category_id,string zhuangtai )
        {
            int startindex = (pageindex - 1) * pagesize;
            string tem = string.Empty;
            string strSql =@"select top " + pagesize + " * from (select row_number() over(order by ag.id desc) as rownumber,ag.*,ca.name as categoryName,le.name as levelName, CASE WHEN getdate()>ag.end_time THEN '已过期'  WHEN ag.state=0 THEN '待授权'  WHEN  ag.state=1 THEN '已授权'  WHEN ag.state=2 THEN '已放弃'  END as states from t_agency as ag left join t_category as ca on ag.category_id=ca.id left join t_level as le on ag.level_id=le.id where ag.valid=1 {0}) as a where 1=1 ";
            if (keys == 0 && keyName != "")
            {
                tem += " and ag.name like'%" + keyName + "%'";
            }
            if (keys == 1 && keyName != "")
            {
                tem += " and ag.mobile like'%" + keyName + "%'";

            }
            if (keys == 2 && keyName != "")
            {
                tem += " and ag.ww_code like'%" + keyName + "%'";

            }
            if (category_id != -1)
            {
                tem += " and ag.category_id=" + category_id + "";
            }
            if (zhuangtai != "请选择")
            {
                if (zhuangtai == "已过期")
                    tem += " and getdate()>ag.end_time ";
                if (zhuangtai == "待授权")
                    tem += " and ag.state=0 ";
                if (zhuangtai == "已授权")
                    tem += " and ag.state=1 ";
                if(zhuangtai=="已放弃")
                    tem += " and ag.state=2";
              
            }
            strSql = string.Format(strSql,tem);
            strSql += " and a.rownumber>" + startindex + " ";
            return DbHelperMySQL.Query(strSql).Tables[0];
        }
        public object GetAgencyBycondtionCount(int keys, string keyName, int category_id, string zhuangtai)
        {
            string strSql = string.Format(@"select count(*) from (select  ag.*,ca.name as categoryName,le.name as levelName,
  CASE 
       WHEN getdate()>ag.end_time THEN '已过期'      
        WHEN ag.state=0 THEN '待授权'
       WHEN  ag.state=1 THEN '已授权'
       WHEN ag.state=2 THEN '已放弃'
       END as states
from t_agency as ag
left join t_category as ca on ag.category_id=ca.id
left join t_level as le on ag.level_id=le.id) as a where 1=1
 and valid=1 ");
            if (keys == 0 && keyName != "")
            {
                strSql += " and name like'%" + keyName + "%'";
            }
            if (keys == 1 && keyName != "")
            {
                strSql += " and mobile like'%" + keyName + "%'";

            }
            if (keys == 2 && keyName != "")
            {
                strSql += " and ww_cod like'%" + keyName + "%'";

            }
            if (category_id != -1)
            {
                strSql += " and category_id=" + category_id + "";
            }
            if (zhuangtai != "请选择")
            {
                strSql += " and states='" + zhuangtai + "'";
            }
           
            return DbHelperMySQL.GetSingle(strSql);
        }
        #endregion

        #region 修改客户信息
        public bool UptAgency(int id, string Name, string companyName, string contract_name, string mobile, string qq, string wxOpenid, string wwCode, string idCard, string shopUrl)
        {
            string strSql = string.Format(@"update t_agency set name='{0}',company_name='{1}',mobile='{2}',qq='{3}',
                                            wx_openid='{4}',ww_code='{5}',id_card='{6}',shop_url='{7}',contract_name='{8}' where
                                            id={9}", Name, companyName, mobile, qq, wxOpenid, wwCode, idCard, shopUrl, contract_name, id);
            int row = DbHelperMySQL.ExecuteSql(strSql);
            if (row > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 查询当前编辑选中的人的信息
        public DataTable GetCustomer(int id)
        {
            string strSql = string.Format(@"select * from t_agency where id ={0}", id);
            return DbHelperMySQL.Query(strSql).Tables[0];
        }
        #endregion

        #region 删除当前客户(逻辑删除)
        public bool delAgency(int id)
        {
            string strSql = string.Format(@"update t_agency set valid=2 where id={0}", id);
            int row = DbHelperMySQL.ExecuteSql(strSql);
            if (row > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 修改授权记录
        public bool UptTime(int id, string levelName, DateTime starTime, DateTime endTime)
        {
            string strSql = string.Format(@"update t_agency set level_name='{0}',start_time='{1}',end_time='{2}' where id={3}", levelName, starTime, endTime, id);
            int row = DbHelperMySQL.ExecuteSql(strSql);
            if (row > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 修改和添加用户判断用户信息是否有重复信息
        /// <summary>
        /// 重复判断
        /// </summary>
        /// <param name="name"></param>
        /// <param name="mobile"></param>
        /// <param name="qq"></param>
        /// <param name="wx_openid"></param>
        /// <param name="ww_code"></param>
        /// <param name="id_card"></param>
        /// <param name="shop_url"></param>
        /// <returns></returns>
        public bool GetSingle(int id,string name,string mobile,string qq,string wx_openid,string ww_code,string id_card,string shop_url)
        {
 
            StringBuilder strSql = new StringBuilder();
            if (id == 0)//表示添加
            {
                strSql.Append("select count(1) from t_agency where valid=1  and (");
                strSql.Append("  name='"+name+"'");
                strSql.Append(" or mobile='"+mobile+"'");
                strSql.Append(" or qq='" + qq + "'");
                strSql.Append(" or wx_openid='" + wx_openid + "'");
                strSql.Append(" or ww_code='" + ww_code + "'");
                strSql.Append(" or shop_url='" + shop_url + "')");
            }
            else //表示修改
            {
                strSql.Append("select count(1) from t_agency where valid=1  and (");
                strSql.Append("  name='" + name + "'");
                strSql.Append(" or mobile='" + mobile + "'");
                strSql.Append(" or qq='" + qq + "'");
                strSql.Append(" or wx_openid='" + wx_openid + "'");
                strSql.Append(" or ww_code='" + ww_code + "'");
                strSql.Append(" or shop_url='" + shop_url + "')");
                strSql.Append(" and id!=" + id + "");
            }
            return DbHelperMySQL.Exists(strSql.ToString());

        }
        #endregion
        #region 修改授权图片和授权编码
        public bool UptSq_img(int id,string img_url)
        {
            
            string strSql = string.Format(@"update t_agency set sq_img='{0}' where id={1}", img_url,id);
            int row = DbHelperMySQL.ExecuteSql(strSql);
            if (row > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public bool UptSq_imgAndCode(int id, string au_code, string img_url)
        {
            string strSql = string.Empty;
            if(!string.IsNullOrEmpty(au_code)){
                strSql = string.Format(@"update t_agency set sq_img='{0}',au_code='{1}' where id={2}", img_url, au_code, id);
            }
            else
                strSql = string.Format(@"update t_agency set sq_img='{0}' where id={1}", img_url, id);
           // string strSql = string.Format(@"update t_agency set sq_img='{0}',au_code='{1}' where id={2}", img_url, au_code, id);
            int row = DbHelperMySQL.ExecuteSql(strSql);
            if (row > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
        #region 得到最大的编码
        /// <summary>
          /// 获取编码最大的实体信息
          /// </summary>
          /// <param name="ag"></param>
          /// <param name="coding"></param>
          /// <returns></returns>
        public NfsModel.agency GetModelCode(agency ag, string coding)
        {
            string sql = @"select top 1 * from (select ag.id,ag.valid,ag.level_id,ag.au_code,le.coding from t_agency as ag
                      left join t_level as le on le.id=ag.level_id ) as a where 1=1  and valid=1  and coding='" + coding + "'  ";
            if (ag.id!= 0)
            {
                sql += "and id!=" + ag.id + "";

            }
            sql += " order by au_code desc ";
            NfsModel.agency model = new NfsModel.agency();
            DataSet ds = DbHelperMySQL.Query(sql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModels(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
         /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public NfsModel.agency DataRowToModels(DataRow row)
        {
            NfsModel.agency model = new NfsModel.agency();
            if (row != null)
            {
                if (row["id"] != null && row["id"].ToString() != "")
                {
                    model.id = int.Parse(row["id"].ToString());
                }
                if (row["au_code"] != null)
                {
                    model.au_code = row["au_code"].ToString();
                }
            }
            return model;
            }
           #endregion
        #region  申请进行授权
        /// <summary>
        /// 进行授权
        /// </summary>
        /// <param name="id"></param>
        /// <param name="state"></param>
        /// <param name="level_id"></param>
        /// <param name="start_time"></param>
        /// <param name="end_time"></param>
        /// <param name="levelName"></param>
        /// <returns></returns>
        public bool UptPassOrNo_pc(int id, int state, int level_id, DateTime start_time, DateTime end_time, string levelName)
        {
            string strSql = string.Format(@"update t_agency set state={0} , level_name='{1}',start_time='{2}',end_time='{3}',level_id={4} where id={5}", state, levelName, start_time, end_time, level_id, id);
            int row = DbHelperMySQL.ExecuteSql(strSql);
            if (row > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion
        #region 导出数据
        public DataTable GetAgencyBycondtionList(int keys, string keyName, int category_id,string zhuangtai )
        {
            string strSql = string.Format(@"select * from (select  ag.id,category_id,level_id,
ag.name,ag.company_name,ag.mobile,ag.contract_name,ag.qq,ag.wx_openid,ag.ww_code,ag.id_card,
ag.shop_url,ag.valid,ag.state,ag.start_time,ag.end_time,ag.sq_img,ag.au_code,ca.name as categoryName,le.name as levelName,
  CASE 
       WHEN getdate()>ag.end_time THEN '已过期'      
        WHEN ag.state=0 THEN '待授权'
       WHEN  ag.state=1 THEN '已授权'
       WHEN ag.state=2 THEN '已放弃'
       END as states
from t_agency as ag
left join t_category as ca on ag.category_id=ca.id
left join t_level as le on ag.level_id=le.id) as a where 1=1
 and valid=1  ");
            if (keys == 0 && keyName!="")
            {
                strSql += " and name like'%" + keyName + "%'";
            }
            if (keys == 1 && keyName != "")
            {
                strSql += " and mobile like'%" + keyName + "%'";

            }
            if (keys == 2 && keyName != "")
            {
                strSql += " and ww_code like'%" + keyName + "%'";

            }
            if (category_id != -1)
            {
                strSql += " and category_id=" + category_id + "";
            }
            if (zhuangtai!="请选择")
            {
                strSql += " and states='" + zhuangtai + "'";
            }
            return DbHelperMySQL.Query(strSql).Tables[0];
        }
        #endregion

        #region (手机)根据登录用户获取客户的信息
        public NfsModel.agency GetModel_account(int act_id)
        {
//            string sql = @"select * from (select ag.*,ac.user_name from t_agency as ag 
//          left join t_account as ac on ag.account_id=ac.id) as a where valid=1 and  account_id=" + act_id + "  limit 0,1";
            string sql = @"select top 1 * from (select ag.*,ac.user_name from t_agency as ag 
          left join t_account as ac on ag.account_id=ac.id) as a where valid=1 and  account_id=" + act_id + "  ";
            NfsModel.agency model = new NfsModel.agency();
            DataSet ds = DbHelperMySQL.Query(sql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
        #endregion

        #region 修改是否审核  /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update_shState(NfsModel.agency model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_agency set ");
         
            if (model.shState != null)
            {
                strSql.Append("shState=" + model.shState + ",");
            }
            else
            {
                strSql.Append("shState= null ,");
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where id=" + model.id + "");
            int rowsAffected = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rowsAffected > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
            #endregion

        #region 根据授权编号或者旺旺号获取用户的授权证书
        public NfsModel.agency GetModel_ByCodes(string codes)
        {
            string sql = @"select top 1 * from t_agency where (au_code='" + codes + "' or ww_code='" + codes + "') and valid=1 ";

            NfsModel.agency model = new NfsModel.agency();
            DataSet ds = DbHelperMySQL.Query(sql.ToString());
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }
            #endregion
        #endregion  MethodEx
    }
}
